package com.stone.business.jc.action;

/**
 * <pre>
 * 绩效考核统计
 * @author 
 * </pre>
 */

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import jxl.read.biff.BiffException;
import jxl.Cell;
import jxl.CellType;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.stone.business.jc.pojo.TJxcp;
import com.stone.business.jc.service.JcCommonService;
import com.stone.para.IConstant;
import com.stone.sys.pojo.TGzltjPara;
import com.stone.utils.DateFormatTool;
import com.stone.utils.FileUtils;
import com.stone.utils.NumUtils;
import com.stone.utils.RandomString;
import com.stone.utils.Resources;
import com.stone.utils.StringUtils;

import frm.ssh.action.BaseListAction;
import frm.ssh.exception.AppException;

public class JxcpAction extends BaseListAction {

	private static final long serialVersionUID = -4647594073681054487L;

	private JcCommonService myService;
	
	TJxcp entity;
	
	private String filenameen = ""; 
	
	// 下载用 返回的文件流
	protected InputStream returnStream;
	// 下载用的名字
	protected String downFileName;
	// 附件表ID
	private String id;
	
	/**
	 * 输入流对象
	 */
	private InputStream is;

	/**
	 * 创建Workbook对象
	 */
	private Workbook rwb;

	/**
	 * 进入详细页面
	 * 
	 * @return
	 * @throws AppException
	 */
	public String initDetail() throws AppException {
		return SUCCESS;
	}

	/**
	 * 进入详细页面
	 * 
	 * @return
	 * @throws AppException
	 */
	public String initTabDetail() throws AppException {

		return "TAB_SUCCESS";
	}

	/**
	 * 统计
	 * 
	 * @return
	 * @throws AppException
	 * @throws IOException 
	 * @throws BiffException 
	 */
	@SuppressWarnings("unchecked")
	public String tongji() throws AppException ,BiffException,IOException{

		// 取得工作量统计参数
		List<TGzltjPara> list = myService.findListByHql("from TGzltjPara");

		TGzltjPara para = list.get(0);

		/*-
		 * 建一个二维数组 
		 * 行为人 列为项目  
		 * 列为要算分的项目数量
		 * 处理完后重新组合数组 把第0列为空的（没有用户的）都过滤掉。
		 */
		List<HashMap<String, Object>> tempList = myService.findListBySql("select max(id) as id from sys_user");

		// 以最大的id号作为行
		int row = Integer.parseInt(String.valueOf(tempList.get(0).get("id"))) + 1;
		
		// 开始结束时间
		int yjks = Integer.parseInt(para.getYjks().replace(":", ""));
		int yjjs = Integer.parseInt(para.getYjjs().replace(":", ""));
		/**
		 * 人员
		 */
		StringBuffer s = new StringBuffer();
	    s.append("select u.id as id,");
		s.append("    u.name as name,");
		s.append("    u.ygbh as ygbh,");
		s.append("    TO_CHAR(u.cssj,'yyyy-MM-dd') as cssj,");
		s.append("    (select dic5.item_Name  from sys_user u1,sys_dictionary dic5 where u1.id > 0 and u.id=u1.id and u1.JXKHGWJJXS = dic5.item_Id and  dic5.item_Type = 'JXKH_GWJJXS') as jxkhgwjjxs,");//by wangtao add20120425
		s.append("    (select dic6.item_Name  from sys_user u1,sys_dictionary dic6 where u1.id > 0 and u.id=u1.id and u1.JXKHJJZSXS = dic6.item_Id and  dic6.item_Type = 'JXKH_JJZSXS') as jxkhjjzsxs,");//by wangtao add20120425
		s.append("    u.jxkhflag as jxkhflag,");
		s.append("    u.zzmmflag as zzmmflag,");
		s.append("    dic1.item_Name as bzname,");
		s.append("    dic2.item_Name as gwname,");
		s.append("    dic3.item_Name as gjname,");
		s.append("    (select dic4.item_Name  from sys_user u1,sys_dictionary dic4 where u1.id > 0 and u.id=u1.id and u1.jxkhjz = dic4.item_Id and  dic4.item_Type = 'JXKH_JZ') as jzname");//by wangtao add20110415
		s.append("  from sys_user u,");
		s.append("    sys_dictionary dic1,");
		s.append("    sys_dictionary dic2,");
		s.append("    sys_dictionary dic3");
		
		s.append(" where u.id > 0");
		s.append("   and u.state = '1'");
		s.append("   and u.jxkhflag in ('1', '2')");
		s.append("   and u.jxkhbz = dic1.item_Id");
		s.append("   and dic1.item_Type = 'JXKH_BZ'");
		s.append("   and u.jxkhgw = dic2.item_Id");
		s.append("   and dic2.item_Type = 'JXKH_GW'");
		s.append("   and u.jxkhgj = dic3.item_Id");
		s.append("   and dic3.item_Type = 'JXKH_GJ'");
		s.append(" order by u.sortid");

		List<HashMap<String, Object>> tempListUser = myService.findListBySql(s.toString());
		
		int i = 0;

		// 为了排序生成的数组 
		int sort[]  = new int[row];
		Arrays.fill(sort, -1);
		
		// 定义统计量数组
		// 计算分数后的
		String str[][] = new String[tempListUser.size()][IConstant.GZLGJ_COL_COUNT];
		String str2[][] = new String[tempListUser.size()][32];//日均工作量
		
		// 先摆上人  sort 索引为用户ID，值为数组序号 从1开始 因为ID最小为1
		for (HashMap<String, Object> map : tempListUser) {
			sort[Integer.parseInt(map.get("id").toString())] = i;
			// 转化一把
			str[i][0] = map.get("name").toString();
			str[i][22] = map.get("jxkhflag").toString();
			str2[i][0] = map.get("name").toString();
			if(map.get("jzname")!=null&&map.get("jzname").toString().equals("班组安全员")){
				str[sort[Integer.parseInt(map.get("id").toString())]][15] = NumUtils.gzltj(str[sort[Integer.parseInt(map.get("id").toString())]][15], 1, 1 , para.getPx11v(), 1);
				
			}else if(map.get("jzname")!=null&&map.get("jzname").toString().equals("班组技术员")){
				str[sort[Integer.parseInt(map.get("id").toString())]][14] = NumUtils.gzltj(str[sort[Integer.parseInt(map.get("id").toString())]][14], 1, 1 , para.getPx21v(), 1);
				
			}else if(map.get("jzname")!=null&&map.get("jzname").toString().equals("班组主持")){
				str[sort[Integer.parseInt(map.get("id").toString())]][13] = NumUtils.gzltj(str[sort[Integer.parseInt(map.get("id").toString())]][13], 1, 1 , para.getPx01v(), 1);
				
			}else if(map.get("jzname")!=null&&map.get("jzname").toString().equals("综合事务")){
				str[sort[Integer.parseInt(map.get("id").toString())]][16] = NumUtils.gzltj(str[sort[Integer.parseInt(map.get("id").toString())]][16], 1, 1 , para.getPx31v(), 1);
				
			}

	
			i++;
		}
		
		/**
		 * 工单工作日期
		 */
		
		conditions = "(state=6 or state=9) and TO_CHAR(rq,'yyyy-MM-dd HH24:mi') >= '" 
			+ super.getSearchBean().getCon2() + " 00:00'"
			+ " AND TO_CHAR(rq,'yyyy-MM-dd HH24:mi') <= '" 
			+ super.getSearchBean().getCon3() + " 23:59' ";
		
		tempList = myService.getListByDHql("czkssj as czkssj, czjssj as czjssj,ycclqk as gzfw, gs as gs, zbz as zbz,zbzname as zbzname,zby as zby,zbyname as zbyname,state as state,rq as rq,gcjs as gcjs", conditions, null, "", "TGzrwd s");

		for (HashMap<String, Object> map : tempList) {
			String state=(String)(map.get("state") == null ? "0":  map.get("state"));//0:9代表有票,5:6代表无票
			// 操作开始日期
			Date czkssjdr = DateFormatTool.formatToDate(DateFormatTool.format(((Date) map.get("czkssj")), false), false);
			// 实际日期
			Date rqdr =DateFormatTool.formatToDate(DateFormatTool.format(((Date) map.get("rq")), false), false);
			long days=1;
			if(state.equals("0")||state.equals("9")){
				days=1;
			}else{
				days= DateFormatTool.dayOfDiff(rqdr, czkssjdr)+1;
			}
			// 工时
			int gs = (Integer) (map.get("gs") == null ? 0 :  map.get("gs"));
			String gzfw=(String)map.get("gzfw");
			// 操作日期判断是否算夜间
			String czkssj = DateFormatTool.formate((Date) map.get("czkssj"), "HHmm");
			String czjssj = DateFormatTool.formate((Date) map.get("czjssj"), "HHmm");
			
			// 总负责人
			String[] zbz = ((String) (map.get("zbz") == null? "" : map.get("zbz"))).split(",");
			// 总负责人姓名
			String zbznames = ((String) (map.get("zbzname") == null? "" : map.get("zbzname")));
			// 成员
			String[] zby = ((String) (map.get("zby") == null? "" : map.get("zby"))).split(",");
			// 成员姓名
			String[] zbynames = ((String) (map.get("zbyname") == null? "" : map.get("zbyname"))).split(",");
			// 如果时间为空 就当 早上 9：30处理 按白天算 。一般不会出现 因为有操作人就有操作时间
			czkssj = "".equals(czkssj) ? "930" : czkssj;
			czjssj = "".equals(czjssj) ? "930" : czjssj;
			// 周末是否
			Date rq = ((Date) (map.get("rq") == null? "" : map.get("rq")));  
	        int n =DateFormatTool.dayOfWeek(rq, false);
			double zmxs=1;
			if (n==6||n==0) {
				zmxs=para.getXk21();
			}
			// 夜间倍数 乘 人/工作 除 实际多少人干了这个工作 
			double yjxs=1;
			if (Integer.parseInt(czkssj) >= yjks || Integer.parseInt(czkssj) <= yjjs || Integer.parseInt(czjssj) >= yjks || Integer.parseInt(czjssj) <= yjjs) {
			yjxs=para.getCz01();
			}
			//工程建设的工作任务单 工时*1.2
			String gcjs=(String)(map.get("gcjs") == null ? "0":  map.get("gcjs"));//1:是工程建设，0,2否
			double gcjsxs=1;
			if(gcjs.equals("1")){
				gcjsxs=1.2;
			}
			// 工作负责人
			for (i = 0; i < zbz.length; i++) {
				
				if ("".equals(zbz[i])) {
					break;
				}
				
				// 20110329
				if (sort[Integer.parseInt(zbz[i])] == -1) {
					continue;
				}
				
			    double fzrxs=1;
				if(StringUtils.isNullOrBlankFull(gzfw)){
				fzrxs=para.getCz71();
				// 不管几个人都按照一次 
				//str[sort[Integer.parseInt(zbz[i])]][7] = NumUtils.gzltj(str[sort[Integer.parseInt(zbz[i])]][7], para.getCz00(), 1, yjxs*para.getCz12(), 1);
				
			      }else{//一种票
			    	  fzrxs=para.getCz61();
			    	// 不管几个人都按照一次 
					//	str[sort[Integer.parseInt(zbz[i])]][6] = NumUtils.gzltj(str[sort[Integer.parseInt(zbz[i])]][6], para.getCz00(), 1, yjxs*para.getCz02(), 1);
						
			      }
				// 实际工时
				str[sort[Integer.parseInt(zbz[i])]][1] = NumUtils.gzltjCzcs(str[sort[Integer.parseInt(zbz[i])]][1],gs,1, days*zmxs*fzrxs*yjxs*gcjsxs, 1, 1);
				// 实际工时得分
				str[sort[Integer.parseInt(zbz[i])]][3] = NumUtils.gzltjCzcs(str[sort[Integer.parseInt(zbz[i])]][3],gs,1, days*zmxs*fzrxs*yjxs*gcjsxs, 1, 1);
				}
			
			// 成员
			for (i = 0; i < zby.length; i++) {
				
				if ("".equals(zby[i])) {
					break;
				}
				
				if (sort[Integer.parseInt(zby[i])] == -1) {
					continue;
				}
				// 如果成员包括负责人，只 算负责人
				if (zbznames.indexOf(zbynames[i]) != -1) {
					continue;
				}
				// 实际工时
				str[sort[Integer.parseInt(zby[i])]][1] = NumUtils.gzltjCzcs(str[sort[Integer.parseInt(zby[i])]][1],gs, 1, days*zmxs*yjxs*gcjsxs, 1, 1);
				// 实际工时得分
				str[sort[Integer.parseInt(zby[i])]][3] = NumUtils.gzltjCzcs(str[sort[Integer.parseInt(zby[i])]][3],gs, 1, days*zmxs*yjxs*gcjsxs, 1, 1);
				if(StringUtils.isNullOrBlankFull(gzfw)){
				// 不管几个人都按照一次 
				//str[sort[Integer.parseInt(zby[i])]][7] = NumUtils.gzltj(str[sort[Integer.parseInt(zby[i])]][7], para.getCz00(), 1, yjxs*para.getCz12(), 1);
				}else{
					// 不管几个人都按照一次 
				//	str[sort[Integer.parseInt(zby[i])]][6] = NumUtils.gzltj(str[sort[Integer.parseInt(zby[i])]][6], para.getCz00(), 1, yjxs*para.getCz02(), 1);
					
				}
				}
		
		}
		/**
		 * 消缺时间
		 */
		
		conditions = "TO_CHAR(xcjssj,'yyyy-MM-dd HH24:mi') >= '" 
			+ super.getSearchBean().getCon2() + " 00:00'"
			+ " AND TO_CHAR(xcjssj,'yyyy-MM-dd HH24:mi') <= '" 
			+ super.getSearchBean().getCon3() + " 23:59' ";
		
		tempList = myService.getListByDHql("xcsj as czkssj, xcjssj as czjssj,xcr as zbz,xqcy as zby", conditions, null, "", "TSbqxjl s");

		for (HashMap<String, Object> map : tempList) {
			

			// 操作日期判断是否算夜间
			String czkssj = DateFormatTool.formate((Date) map.get("czkssj"), "HHmm");
			String czjssj = DateFormatTool.formate((Date) map.get("czjssj"), "HHmm");
			
			// 总负责人
			String[] zbz = ((String) (map.get("zbz") == null? "" : map.get("zbz"))).split(",");
			
			// 成员
			String[] zby = ((String) (map.get("zby") == null? "" : map.get("zby"))).split(",");
			// 如果时间为空 就当 早上 9：30处理 按白天算 。一般不会出现 因为有操作人就有操作时间
			czkssj = "".equals(czkssj) ? "930" : czkssj;
			czjssj = "".equals(czjssj) ? "930" : czjssj;
			
			// 夜间倍数 乘 人/工作 除 实际多少人干了这个工作 
			double yjxs=1;
			if (Integer.parseInt(czkssj) >= yjks || Integer.parseInt(czkssj) <= yjjs || Integer.parseInt(czjssj) >= yjks || Integer.parseInt(czjssj) <= yjjs) {
			yjxs=para.getQx01();
			}
			
			// 工作负责人
			for (i = 0; i < zbz.length; i++) {
				
				if ("".equals(zbz[i])) {
					break;
				}
				
				// 20110329
				if (sort[Integer.parseInt(zbz[i])] == -1) {
					continue;
				}
				
				// 不管几个人都按照一次 
				str[sort[Integer.parseInt(zbz[i])]][17] = NumUtils.gzltj(str[sort[Integer.parseInt(zbz[i])]][17], para.getQx00(), 1, yjxs*para.getQx02(), 1);
				
				}
			
			// 成员
			for (i = 0; i < zby.length; i++) {
				
				if ("".equals(zby[i])) {
					break;
				}
				
				if (sort[Integer.parseInt(zby[i])] == -1) {
					continue;
				}
				str[sort[Integer.parseInt(zby[i])]][17] = NumUtils.gzltj(str[sort[Integer.parseInt(zby[i])]][17], para.getQx00(), 1, yjxs*para.getQx02(), 1);
				}
		}
		/**
		 * 临时管理工作安排班组
		 */
		conditions = "TO_CHAR(startdate,'yyyy-MM-dd HH24:mi') >= '" 
			+ super.getSearchBean().getCon2() + " 00:00'"
			+ " AND TO_CHAR(startdate,'yyyy-MM-dd HH24:mi') <= '" 
			+ super.getSearchBean().getCon3() + " 23:59' and plantype=2";
		
		tempList = myService.getListByDHql("startdate as czkssj, enddate as czjssj,fzr as zbz,gs as gs", conditions, null, "", "TPlanWeekDetail s");

		for (HashMap<String, Object> map : tempList) {
			
			// 工时
			int gs = (Integer) (map.get("gs") == null ? 0 :  map.get("gs"));
			// 操作日期判断是否算夜间
			String czkssj = DateFormatTool.formate((Date) map.get("czkssj"), "HHmm");
			String czjssj = DateFormatTool.formate((Date) map.get("czjssj"), "HHmm");
			
			// 总负责人
			String[] zbz = ((String) (map.get("zbz") == null? "" : map.get("zbz"))).split(",");
			
			// 如果时间为空 就当 早上 9：30处理 按白天算 。一般不会出现 因为有操作人就有操作时间
			czkssj = "".equals(czkssj) ? "930" : czkssj;
			czjssj = "".equals(czjssj) ? "930" : czjssj;
			// 周末是否
			Date rq = ((Date) (map.get("czkssj") == null? "" : map.get("czkssj")));  
	        int n =DateFormatTool.dayOfWeek(rq, false);
			double zmxs=1;
			if (n==6||n==0) {
				zmxs=para.getXk21();
			}
			// 夜间倍数 乘 人/工作 除 实际多少人干了这个工作 
			double yjxs=1;
			if (Integer.parseInt(czkssj) >= yjks || Integer.parseInt(czkssj) <= yjjs || Integer.parseInt(czjssj) >= yjks || Integer.parseInt(czjssj) <= yjjs) {
			yjxs=para.getQx01();
			}
			
			// 工作负责人
			for (i = 0; i < zbz.length; i++) {
				
				if ("".equals(zbz[i])) {
					break;
				}
				
				// 20110329
				if (sort[Integer.parseInt(zbz[i])] == -1) {
					continue;
				}
				// 实际工时
				str[sort[Integer.parseInt(zbz[i])]][1] = NumUtils.gzltjCzcs(str[sort[Integer.parseInt(zbz[i])]][1],gs,1, zmxs, 1, 1);
				// 实际工时得分
				str[sort[Integer.parseInt(zbz[i])]][3] = NumUtils.gzltjCzcs(str[sort[Integer.parseInt(zbz[i])]][3],gs,1, zmxs, 1, 1);
				
				
				}
			
			
		}
		/**
		 * 班组作业控制卡(一种票)
		 */
		StringBuffer sql = new StringBuffer();
		
		conditions = "TO_CHAR(DT_CZRQ,'yyyy-MM-dd HH24:mi') >= '" 
		+ super.getSearchBean().getCon2() + " 00:00'"
		+ " AND TO_CHAR(DT_CZRQ,'yyyy-MM-dd HH24:mi') <= '" 
		+ super.getSearchBean().getCon3() + " 23:59' ";
		sql.append("SELECT SUM(C) COUNT, CJRID AS CJRID, CJR AS CJR FROM ( ");
		//班组作业控制卡
		sql.append(" SELECT COUNT(*) AS C, CJRID AS CJRID, BXR AS CJR FROM TAB_KZK WHERE " + conditions + " AND DT_CZRQ IS NOT NULL AND OC_TYPE='01' GROUP BY CJRID,BXR ");	
     	sql.append(" ) GROUP BY CJRID, CJR ");
		tempList = myService.findListBySql(sql.toString());
		
		// 录入直接查询出人的所有次数 直接 乘以 分值  人次 和夜间都不算了
		for (HashMap<String, Object> map : tempList) {
			if (sort[Integer.parseInt(map.get("cjrid").toString())] == -1) {
				continue;
			}	
		str[sort[Integer.parseInt(map.get("cjrid").toString())]][9] = NumUtils.gzltj(str[sort[Integer.parseInt(map.get("cjrid").toString())]][9], 1, 1 , NumUtils.parseInt(map.get("count").toString()) * para.getCz32(), 1);			
		}
		/**
		 * 工作计划(一种票)有停电范围的是一种票
		 */
		 sql = new StringBuffer();
		
		conditions = "TO_CHAR(STARTDATE,'yyyy-MM-dd HH24:mi') >= '" 
		+ super.getSearchBean().getCon2() + " 00:00'"
		+ " AND TO_CHAR(STARTDATE,'yyyy-MM-dd HH24:mi') <= '" 
		+ super.getSearchBean().getCon3() + " 23:59' ";
		sql.append("SELECT SUM(C) COUNT, CJRID AS CJRID, CJR AS CJR FROM ( ");
		sql.append(" SELECT COUNT(*) AS C, fzr AS CJRID, RESPONSIBLE AS CJR FROM TAB_PLAN WHERE " + conditions + "  AND POWERRANGE is not null GROUP BY fzr,RESPONSIBLE ");	
     	sql.append(" ) GROUP BY CJRID, CJR ");
		tempList = myService.findListBySql(sql.toString());
		
		// 录入直接查询出人的所有次数 直接 乘以 分值  人次 和夜间都不算了
		for (HashMap<String, Object> map : tempList) {
			if (sort[Integer.parseInt(map.get("cjrid").toString())] == -1) {
				continue;
			}	
			str[sort[Integer.parseInt(map.get("cjrid").toString())]][6] = String.valueOf((NumUtils.parseInt(map.get("count").toString()))*para.getCz02());
			//风险控制卡和一种票计分一样
			str[sort[Integer.parseInt(map.get("cjrid").toString())]][11] = String.valueOf((NumUtils.parseInt(map.get("count").toString()))*para.getCz02());
			
		}
		/**
		 * 班组作业控制卡(二种票)
		 */
		 sql = new StringBuffer();
		
		conditions = "TO_CHAR(DT_CZRQ,'yyyy-MM-dd HH24:mi') >= '" 
		+ super.getSearchBean().getCon2() + " 00:00'"
		+ " AND TO_CHAR(DT_CZRQ,'yyyy-MM-dd HH24:mi') <= '" 
		+ super.getSearchBean().getCon3() + " 23:59' ";
		sql.append("SELECT SUM(C) COUNT, CJRID AS CJRID, CJR AS CJR FROM ( ");
		//班组作业控制卡
		sql.append(" SELECT COUNT(*) AS C, CJRID AS CJRID, BXR AS CJR FROM TAB_KZK WHERE " + conditions + " AND DT_CZRQ IS NOT NULL AND OC_TYPE='02' GROUP BY CJRID,BXR ");	
     	sql.append(" ) GROUP BY CJRID, CJR ");
		tempList = myService.findListBySql(sql.toString());
		
		// 录入直接查询出人的所有次数 直接 乘以 分值  人次 和夜间都不算了
		for (HashMap<String, Object> map : tempList) {
			if (sort[Integer.parseInt(map.get("cjrid").toString())] == -1) {
				continue;
			}
			str[sort[Integer.parseInt(map.get("cjrid").toString())]][9] = NumUtils.gzltj(str[sort[Integer.parseInt(map.get("cjrid").toString())]][9], 1, 1 , NumUtils.parseInt(map.get("count").toString()) * para.getCz32(), 1);			
			}
		/**
		 * 工作计划(二种票)无停电范围的是二种票
		 */
		 sql = new StringBuffer();
		
		conditions = "TO_CHAR(STARTDATE,'yyyy-MM-dd HH24:mi') >= '" 
		+ super.getSearchBean().getCon2() + " 00:00'"
		+ " AND TO_CHAR(STARTDATE,'yyyy-MM-dd HH24:mi') <= '" 
		+ super.getSearchBean().getCon3() + " 23:59' ";
		sql.append("SELECT SUM(C) COUNT, CJRID AS CJRID, CJR AS CJR FROM ( ");
		sql.append(" SELECT COUNT(*) AS C, fzr AS CJRID, RESPONSIBLE AS CJR FROM TAB_PLAN WHERE " + conditions + "  AND POWERRANGE is null GROUP BY fzr,RESPONSIBLE ");	
     	sql.append(" ) GROUP BY CJRID, CJR ");
		tempList = myService.findListBySql(sql.toString());
		
		// 录入直接查询出人的所有次数 直接 乘以 分值  人次 和夜间都不算了
		for (HashMap<String, Object> map : tempList) {
			if (sort[Integer.parseInt(map.get("cjrid").toString())] == -1) {
				continue;
			}	
			str[sort[Integer.parseInt(map.get("cjrid").toString())]][7] = String.valueOf((NumUtils.parseInt(map.get("count").toString()))*para.getCz12());
			
		}
		/**
		 * 工作任务单录入人(录入时间在工作日期前一天15:00以前统计0.1分)
		 */
		 sql = new StringBuffer();
		
		conditions = "TO_CHAR(rq,'yyyy-MM-dd HH24:mi') >= '" 
		+ super.getSearchBean().getCon2() + " 00:00'"
		+ " AND TO_CHAR(rq,'yyyy-MM-dd HH24:mi') <= '" 
		+ super.getSearchBean().getCon3() + " 23:59' AND TO_CHAR(CJSJ,'yyyy-MM-dd HH24:mi')<concat(TO_CHAR(rq-1,'yyyy-MM-dd'),' 15:00')";
		sql.append("SELECT SUM(C) COUNT, CJRID AS CJRID, CJR AS CJR FROM ( ");
		sql.append(" SELECT COUNT(*) AS C, CJRID AS CJRID, CJR AS CJR FROM TAB_GZRWD WHERE " + conditions + " GROUP BY CJRID,CJR ");	
     	sql.append(" ) GROUP BY CJRID, CJR ");
		tempList = myService.findListBySql(sql.toString());
		
		// 录入直接查询出人的所有次数 直接 乘以 分值  人次 和夜间都不算了
		for (HashMap<String, Object> map : tempList) {
			if (sort[Integer.parseInt(map.get("cjrid").toString())] == -1) {
				continue;
			}
			str[sort[Integer.parseInt(map.get("cjrid").toString())]][12] = NumUtils.gzltj(str[sort[Integer.parseInt(map.get("cjrid").toString())]][12], 1, 1 , NumUtils.parseInt(map.get("count").toString()) *0.1, 1);			
			
		}
		/**
		 * 工序质量控制卡
		 */
		 sql = new StringBuffer();
		
		conditions = "TO_CHAR(DT_JHGZSJQ,'yyyy-MM-dd HH24:mi') >= '" 
		+ super.getSearchBean().getCon2() + " 00:00'"
		+ " AND TO_CHAR(DT_JHGZSJQ,'yyyy-MM-dd HH24:mi') <= '" 
		+ super.getSearchBean().getCon3() + " 23:59' ";
		sql.append("SELECT SUM(C) COUNT, CJRID AS CJRID, CJR AS CJR FROM ( ");
		//工序质量控制卡
		sql.append(" SELECT COUNT(*) AS C, CJRID AS CJRID, OC_BXR AS CJR FROM TAB_GXZLKZK WHERE " + conditions + " AND DT_JHGZSJQ IS NOT NULL GROUP BY CJRID, OC_BXR ");	
     	sql.append(" ) GROUP BY CJRID, CJR ");
		tempList = myService.findListBySql(sql.toString());
		
		// 录入直接查询出人的所有次数 直接 乘以 分值  人次 和夜间都不算了
		for (HashMap<String, Object> map : tempList) {
			if (sort[Integer.parseInt(map.get("cjrid").toString())] == -1) {
				continue;
			}	
			str[sort[Integer.parseInt(map.get("cjrid").toString())]][10] = String.valueOf((NumUtils.parseInt(map.get("count").toString()))*para.getCz42());
			}

		/**
		 * 录入人 录入时间
		 */
		 sql = new StringBuffer();
		
		conditions = "TO_CHAR(cjsj,'yyyy-MM-dd HH24:mi') >= '" 
		+ super.getSearchBean().getCon2() + " 00:00'"
		+ " AND TO_CHAR(cjsj,'yyyy-MM-dd HH24:mi') <= '" 
		+ super.getSearchBean().getCon3() + " 23:59' ";
		conditions1 = "TO_CHAR(JLRQ,'yyyy-MM-dd HH24:mi') >= '" 
			+ super.getSearchBean().getCon2() + " 00:00'"
			+ " AND TO_CHAR(JLRQ,'yyyy-MM-dd HH24:mi') <= '" 
			+ super.getSearchBean().getCon3() + " 23:59' ";
		conditions1 = "TO_CHAR(RQ,'yyyy-MM-dd HH24:mi') >= '" 
			+ super.getSearchBean().getCon2() + " 00:00'"
			+ " AND TO_CHAR(RQ,'yyyy-MM-dd HH24:mi') <= '" 
			+ super.getSearchBean().getCon3() + " 23:59' ";
		sql.append("SELECT SUM(C) COUNT, CJRID AS CJRID, CJR AS CJR FROM ( ");
		//工作记录
		sql.append(" SELECT COUNT(*) AS C, CJRID AS CJRID, JLR AS CJR FROM TAB_GZJL WHERE GZRWDID IN (SELECT ID FROM TAB_GZRWD WHERE " + conditions1 + ") AND JLRQ IS NOT NULL GROUP BY CJRID, JLR ");	
//		by wangtao add2020427
		// 设备缺陷记录
		sql.append(" UNION ALL");
		sql.append(" SELECT COUNT(*) AS C, CJRID AS CJRID, CJR AS CJR FROM TAB_SBQXJL WHERE " + conditions + " GROUP BY CJRID, CJR ");	
//		 设备缺陷记录录入人1
		sql.append(" UNION ALL");
		sql.append(" SELECT COUNT(*) AS C, CJRID1 AS CJRID, CJR1 AS CJR FROM TAB_SBQXJL WHERE " + conditions + " AND CJRID1 IS NOT NULL GROUP BY CJRID1, CJR1 ");	
		sql.append(" ) GROUP BY CJRID, CJR ");

		tempList = myService.findListBySql(sql.toString());
		
		// 录入直接查询出人的所有次数 直接 乘以 分值  人次 和夜间都不算了
		for (HashMap<String, Object> map : tempList) {
			if (sort[Integer.parseInt(map.get("cjrid").toString())] == -1) {
				continue;
			}	
			str[sort[Integer.parseInt(map.get("cjrid").toString())]][12] = NumUtils.gzltj(str[sort[Integer.parseInt(map.get("cjrid").toString())]][12], 1, 1 , NumUtils.parseInt(map.get("count").toString())*para.getCz52(), 1);			
			}
		conditions = "TO_CHAR(khrq,'yyyy-MM-dd HH24:mi') >= '" + super.getSearchBean().getCon2() + " 00:00'"
		+ " AND TO_CHAR(khrq,'yyyy-MM-dd HH24:mi') <= '" + super.getSearchBean().getCon3() + " 23:59' ";
		
		// 工作质量得分
		tempList = myService.getListByDHql("sum(fs) as fs, userid as userid", conditions, null, "group by userid", "TGzzlkh");
		for (HashMap<String, Object> map : tempList) {
		
			if (sort[Integer.parseInt(map.get("userid").toString())] == -1) {
				continue;
			}
			
			str[sort[Integer.parseInt(map.get("userid").toString())]][19] = String.valueOf((NumUtils.parseDouble(map.get("fs").toString())));
		}	
		
		// 专项考核
		tempList = myService.getListByDHql("sum(fs) as fs, userid as userid", conditions, null, "group by userid", "TZxkh");
		for (HashMap<String, Object> map : tempList) {
			
			if (sort[Integer.parseInt(map.get("userid").toString())] == -1) {
				continue;
			}
			
			str[sort[Integer.parseInt(map.get("userid").toString())]][20] = String.valueOf((NumUtils.parseDouble(map.get("fs").toString())));
		}			
		// 班组考评
		List<HashMap<String, Object>> tempbzkpList = myService.getListByDHql("sum(fs) as fs, bzid as bzid", conditions, null, "group by bzid order by bzid", "TZxkhbz");
		
		// 考勤天数累计
		conditions = "TO_CHAR(kqrq,'yyyy-MM-dd HH24:mi') >= '" + super.getSearchBean().getCon2() + " 00:00'"
		+ " AND TO_CHAR(kqrq,'yyyy-MM-dd HH24:mi') <= '" + super.getSearchBean().getCon3() + " 23:59' ";
		tempList = myService.getListByDHql("sum(qqts) as qqts, userid as userid", conditions, null, "group by userid", "TKq");
		for (HashMap<String, Object> map : tempList) {
			
			if (sort[Integer.parseInt(map.get("userid").toString())] == -1) {
				continue;
			}
			
			str[sort[Integer.parseInt(map.get("userid").toString())]][21] = String.valueOf(NumUtils.parseInt((map.get("qqts") == null ? "" :map.get("qqts").toString())));
			//str[sort[Integer.parseInt(map.get("userid").toString())]][6] = map.get("qqyy") == null ? "" :map.get("qqyy").toString();
		}	
		// 考勤原因by wangtao add20120308
		conditions = "TO_CHAR(kqrq,'yyyy-MM-dd HH24:mi') >= '" + super.getSearchBean().getCon2() + " 00:00'"
		+ " AND TO_CHAR(kqrq,'yyyy-MM-dd HH24:mi') <= '" + super.getSearchBean().getCon3() + " 23:59' ";
		tempList = myService.getListByDHql("qqyy as qqyy, userid as userid", conditions, null, "", "TKq");
		for (HashMap<String, Object> map : tempList) {
			
			if (sort[Integer.parseInt(map.get("userid").toString())] == -1) {
				continue;
			}
			//str[sort[Integer.parseInt(map.get("userid").toString())]][6] = str[sort[Integer.parseInt(map.get("userid").toString())]][6]+(map.get("qqyy") == null ? "" :map.get("qqyy").toString());
			}
		// 参与考核
		List<String[]> exp1 = new ArrayList();
		// 不参与考核
		List<String[]> exp2 = new ArrayList();
		// 日均工作量
		List<String[]> exp3 = new ArrayList();
		for (i=0; i<str.length;i++) {
				// 未参与工作量考核人员 就是不参与的和未值班>21天的
			if ("2".equals(str[i][22])) {
				exp2.add(str[i]);
			} else {
				exp1.add(str[i]);
				exp3.add(str2[i]);
			}
		}
		
		this.createExcel(tempListUser, exp1, exp2,exp3,tempbzkpList);
	
		if (this.entity == null) {
			entity = new TJxcp();
		}
		
		entity.setFilenamecn(Resources.getString("jxcpname", "struts"));
		entity.setFilenameen(filenameen);
		entity.setTjsjks(DateFormatTool.formatToDate(super.getSearchBean().getCon2(), "yyyy-MM-dd"));
		entity.setTjsjjs(DateFormatTool.formatToDate(super.getSearchBean().getCon3(), "yyyy-MM-dd"));
		myService.save(entity);
		super.setInfo("统计失败");
		return SUCCESS;
	}
	
	/**
	 * 读取模版文件形成输入流对象。
	 * 
	 * @param sheetname 路径及文件名称
	 */
	private void readExcel(String sheetname) {
		try {

			// 创建Workbook对象 只读Workbook对象
			// 直接从本地文件创建Workbook
			// 从输入流创建Workbook
			is = new FileInputStream(sheetname);
			rwb = Workbook.getWorkbook(is);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void closeIs() {
		try {
			this.is.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static void EditExcel(jxl.write.WritableSheet ws, int col, int row,
			   String value) throws Exception { // 字符串类型
			  jxl.write.Label labelC = new jxl.write.Label(col, row, value);
			  ws.addCell(labelC);
			 }
	public static void EditExcel(jxl.write.WritableSheet ws, int col, int row,
			   double value) throws Exception { // 添加 Number 对象 by wangtao add20110415
              jxl.write.Number labelN = new jxl.write.Number(col, row, value);
			  ws.addCell(labelN);
			 }

	/**
	 * 下载
	 * 
	 * @throws FileNotFoundException
	 */
	public String fileDownload() throws AppException, FileNotFoundException {

		TJxcp entity = (TJxcp) myService.findById(Long
				.parseLong(this.id), TJxcp.class.getName());

		returnStream = new java.io.FileInputStream( Resources.getString("jxcppath", "struts") + entity.getFilenameen());

		downFileName = entity.getFilenamecn();
		
		return "DOWNLOAD_SUCCESS";

	}
	
	/**
	 * 下载时需要显示的名字
	 */
	public String getDownloadFileName() throws Exception {

		downFileName = new String(downFileName.getBytes(), "ISO8859-1") + ".xls";

		return downFileName;

	}	
	
	/**
	 * 删除
	 * @return
	 * @throws AppException
	 */
	public String delete() throws AppException {
		
		myService.deleteJxcp(id);
		
		return "DEL-SUCCESS";
		
	}

	public void setMyService(JcCommonService myService) {
		this.service = myService;
		this.myService = myService;
	}

	public TJxcp getEntity() {
		return entity;
	}

	public void setEntity(TJxcp entity) {
		this.entity = entity;
	}

	public InputStream getReturnStream() {
		return returnStream;
	}

	public String getDownFileName() {
		return downFileName;
	}

	public void setId(String id) {
		this.id = id;
	}

	public void setReturnStream(InputStream returnStream) {
		this.returnStream = returnStream;
	}

	public void setDownFileName(String downFileName) {
		this.downFileName = downFileName;
	}
	
	/**
	 * 设置查询条件
	 */
	public HashMap<String, Object> setSearchCondition() {
		return null;
	}
	
	// 员工信息
	private List<HashMap<String,Object>> jxcpUserList;
	
	// 考勤情况
	private List<HashMap<String,Object>> jxcpKqList;
	
	// 工作质量考核记录
	private List<HashMap<String,Object>> jxcpGzzlkhList;
	
	// 专项考核
	private List<HashMap<String,Object>> jxcpZxkhList;

	String conditions = "";
	String conditions1 = "";
	/**
	 * 导出前信息查看员工信息
	 */
	public String getUserList() {
/*		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon2())) {
			conditions += " AND TO_CHAR(k.kqrq,'yyyy-MM-dd') >= '" + super.getSearchBean().getCon2() + "'";
		}
		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon3())) {
			conditions += " AND TO_CHAR(k.kqrq,'yyyy-MM-dd') <= '" + super.getSearchBean().getCon3() + "'";
		}
		
*/		
		/**
		 * 人员
		 */
		StringBuffer s = new StringBuffer();
	    s.append("select u.id as id,");
		s.append("    u.name as name,");
		s.append("    u.ygbh as ygbh,");
		s.append("    u.zzmmflag as zzmmflag,");
		s.append("    u.jxkhflag as jxkhflag,");

		// 人员信息未参加工作量考核原因
		s.append("    case when u.jxkhflag = '2' then u.bkhyy");
		s.append("    else (case when (select sum(qqts)  from tab_kq kq where kq.userid = u.id ");//by wangtao update20120308考勤统计好几个月
		s.append("           and TO_CHAR(kqrq, 'yyyy-MM-dd HH24:mi') >= '" + super.getSearchBean().getCon2() + " 00:00'");
		s.append("           AND TO_CHAR(kqrq, 'yyyy-MM-dd HH24:mi') <= '" + super.getSearchBean().getCon3() + " 23:59' group by kq.userid) > 21 then '未值班天数大于21天' else '' end )");
		s.append("    end as bkhyy,");
		
//		s.append("    (select qqyy from tab_kq kq where kq.userid = u.id");
//		s.append("           and TO_CHAR(kqrq, 'yyyy-MM-dd HH24:mi') >= '" + super.getSearchBean().getCon2() + " 00:00'");
//		s.append("           AND TO_CHAR(kqrq, 'yyyy-MM-dd HH24:mi') <= '" + super.getSearchBean().getCon3() + " 23:59' group by qqyy) as qqyy,");	
//		s.append("    (select qqts from tab_kq kq where kq.userid = u.id");
//		s.append("           and TO_CHAR(kqrq, 'yyyy-MM-dd HH24:mi') >= '" + super.getSearchBean().getCon2() + " 00:00'");
//		s.append("           AND TO_CHAR(kqrq, 'yyyy-MM-dd HH24:mi') <= '" + super.getSearchBean().getCon3() + " 23:59') as qqts,");		
		s.append("    dic1.item_Name as bzname,");
		s.append("    dic2.item_Name as gwname,");
		s.append("    dic3.item_Name as gjname,");
		s.append("    (select dic4.item_Name  from sys_user u1,sys_dictionary dic4 where u1.id > 0 and u.id=u1.id and u1.jxkhjz = dic4.item_Id and dic4.item_Type = 'JXKH_JZ') as jzname");//by wangtao add20110415
		s.append("  from sys_user u,");
		s.append("    sys_dictionary dic1,");
		s.append("    sys_dictionary dic2,");
		s.append("    sys_dictionary dic3");
		
		s.append(" where u.id > 0");
		s.append("   and u.state = '1'");
		s.append("   and u.jxkhflag in ('1', '2')");
		s.append("   and u.jxkhbz = dic1.item_Id");
		s.append("   and dic1.item_Type = 'JXKH_BZ'");
		s.append("   and u.jxkhgw = dic2.item_Id");
		s.append("   and dic2.item_Type = 'JXKH_GW'");
		s.append("   and u.jxkhgj = dic3.item_Id");
		s.append("   and dic3.item_Type = 'JXKH_GJ'");
		/*s.append("   and u.jxkhjz = dic4.item_Id");//by wangtao add20110415
		s.append("   and dic4.item_Type = 'JXKH_JZ'");*/
		/*s.append("  from sys_user u,");
		s.append("    sys_dictionary dic1,");
		s.append("    sys_dictionary dic2,");
		s.append("    sys_dictionary dic3");
		s.append(" where u.id > 0");
		s.append("   and u.state = '1'");
		s.append("   and u.jxkhflag in ('1', '2')");
		s.append("   and u.jxkhbz = dic1.item_Id");
		s.append("   and dic1.item_Type = 'JXKH_BZ'");
		s.append("   and u.jxkhgw = dic2.item_Id");
		s.append("   and dic2.item_Type = 'JXKH_GW'");
		s.append("   and u.jxkhgj = dic3.item_Id");
		s.append("   and dic3.item_Type = 'JXKH_GJ'");*/
		s.append(" order by u.sortid");

		jxcpUserList = myService.findListBySql(s.toString());
		
		
		
		
		
/*		
		
		jxcpUserList = new ArrayList<HashMap<String,Object>>();
		
		// 参与绩效但是不考核的
		//List<HashMap<String,Object>> tempList = myService.getListByDHql("u.name as username, '0' as khflag, '0' as khyy, k.khrq as khrq", "u.id = k.userid and u.jxkhflag = '2'" + conditions, null, "order by u.sortid", "TKq k, TUser u");
		List<HashMap<String,Object>> tempList = myService.getListByDHql("u.name as username, '0' as khflag, bkhyy as khyy", "u.jxkhflag = '2'", null, "order by u.sortid", "TUser u");
		jxcpUserList.addAll(tempList);
		
		// 参与绩效但是缺勤21天以上的
		tempList = myService.getListByDHql("u.name as username, '0' as khflag, k.qqyy || '缺勤超过21天' as khyy, k.kqrq as kqrq", "u.id = k.userid and u.jxkhflag = '1' and k.qqts >= 21" + conditions, null, "order by u.sortid", "TKq k, TUser u");
		jxcpUserList.addAll(tempList);
		
		// 参与绩效并且缺勤21天以下的
		tempList = myService.getListByDHql("u.name as username, '1' as khflag, '' as khyy, k.kqrq as kqrq", "u.id = k.userid and u.jxkhflag = '1' and k.qqts < 21" + conditions, null, "order by u.sortid", "TKq k, TUser u");
		jxcpUserList.addAll(tempList);
*/
		
		return "JXCP_USER_SUCCESS";
	}
	
	/**
	 * 导出前信息查看考勤情况
	 */
	public String getKqList() {
		
		conditions = "";
		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon2())) {
			conditions += " AND TO_CHAR(k.kqrq,'yyyy-MM-dd') >= '" + super.getSearchBean().getCon2() + "'";
		}
		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon3())) {
			conditions += " AND TO_CHAR(k.kqrq,'yyyy-MM-dd') <= '" + super.getSearchBean().getCon3() + "'";
		}
	
		this.jxcpKqList = myService.getListByDHql("k.kqrq as kqrq, k.qqyy as qqyy, k.qqts as qqts, u.name as username", "u.id = k.userid" + conditions, null, "order by u.sortid", "TKq k, TUser u");
		return "JXCP_KQ_SUCCESS";
	}
	
	/**
	 * 导出前信息查看工作质量考核记录
	 */
	public String getGzzlkhList() {
		
		conditions = "";
		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon2())) {
			conditions += " AND TO_CHAR(k.khrq,'yyyy-MM-dd') >= '" + super.getSearchBean().getCon2() + "'";
		}
		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon3())) {
			conditions += " AND TO_CHAR(k.khrq,'yyyy-MM-dd') <= '" + super.getSearchBean().getCon3() + "'";
		}
		
		this.jxcpGzzlkhList = myService.getListByDHql("k.fs as fs, k.khxzremark as khxzremark, k.khrq as khrq, u.name as username", "u.id = k.userid" + conditions, null, "order by u.sortid", "TGzzlkh k, TUser u");
		return "JXCP_GZZLKH_SUCCESS";
	}
	
	/**
	 * 导出前信息查看专项考核
	 */
	public String getZxkhList() {
		
		conditions = "";
		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon2())) {
			conditions += " AND TO_CHAR(k.khrq,'yyyy-MM-dd') >= '" + super.getSearchBean().getCon2() + "'";
		}
		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon3())) {
			conditions += " AND TO_CHAR(k.khrq,'yyyy-MM-dd') <= '" + super.getSearchBean().getCon3() + "'";
		}

		this.jxcpZxkhList = myService.getListByDHql("k.fsyy as fsyy, k.fs as fs, k.khrq as khrq, u.name as username", "u.id = k.userid " + conditions, null, "order by u.sortid", "TZxkh k, TUser u");
		return "JXCP_ZXKH_SUCCESS";
	}
	/**
	 * 读取模版文件加入数据形成报表文件。
	 * 
	 * @param path 路径及文件名称
	 * @param list 数据列表 数组
	 */
	private String createNewExcel(String sheetname, List<HashMap<String,Object>> listUser, List<String[]> list1,List<String[]> list2,List<String[]> list3,List<HashMap<String,Object>> listBzkp) {
		
		String rtn = "0";
		
		try { 

			// 利用已经创建的Excel工作薄创建新的可写入的Excel工作薄
 			WritableWorkbook wwb = Workbook.createWorkbook(new File(sheetname), rwb);
			// 读取第一个sheet工作表
			WritableSheet ws1 = wwb.getSheet(1);

			// 员工基础信息表
			for (int i = 0; i < listUser.size(); i++) {
				
				HashMap<String,Object> map = listUser.get(i);
			
				// 姓名
				EditExcel(ws1, 0, i+1, (String)map.get("name"));
				// 员工号
				EditExcel(ws1, 7, i+1, (String)map.get("ygbh"));
				// 班组
				EditExcel(ws1, 1, i+1, (String)map.get("bzname"));
				// 奖金系数
				//EditExcel(ws1, 3, i+2, map.get("jxkhjjxs") == null ? "0" : map.get("jxkhjjxs").toString());
				EditExcel(ws1, 2, i+1, map.get("jxkhgwjjxs") == null ? 0 :NumUtils.parseDouble(map.get("jxkhgwjjxs").toString()) );
				// 岗位
				EditExcel(ws1, 3, i+1, (String)map.get("gwname"));
				// 岗级
				EditExcel(ws1, 4, i+1, (String)map.get("gjname"));
				// 兼职	
				EditExcel(ws1, 5, i+1, (String)map.get("jzname"));//by wangtao add 20110415
				String zzmm="";
				if("0".equals(map.get("zzmmflag"))){
					zzmm="党员";
				}else if("1".equals(map.get("zzmmflag"))){
					zzmm="团员";
				}else{
					zzmm="群众";
				}
				// 政治面貌
				EditExcel(ws1, 10, i+1, zzmm);
				EditExcel(ws1, 11, i+1, (map.get("cssj") == null? "" : (String)map.get("cssj")));
				// 奖金折算系数
				//EditExcel(ws1, 12, i+1, (map.get("jxkhjjzsxs") == null? "" : (String)map.get("jxkhjjzsxs")));
				EditExcel(ws1, 12, i+1, map.get("jxkhjjzsxs") == null ? 0 :NumUtils.parseDouble(map.get("jxkhjjzsxs").toString()) );
				

			}
			
			WritableSheet ws2 = wwb.getSheet(4);
			
			// 日期
			//EditExcel(ws2,5, 1, super.getSearchBean().getCon2());
			//EditExcel(ws2,6, 1, super.getSearchBean().getCon3());
			EditExcel(ws2,0, 0, ws2.getCell(0,0).getContents()+super.getSearchBean().getCon2() + " 00:00"+"<------>"+super.getSearchBean().getCon3() + " 23:59");
			
			// 参与考核的绩效得分表 sheet2 左侧
			for (int i = 0; i < list1.size(); i++) {
				for (int j = 0; j < IConstant.GZLGJ_COL_COUNT-6; j++) {
						if (j == 0 ) {
						EditExcel(ws2,j, i+2, list1.get(i)[j]);
					}else if(j!=0&&j!=18){//by wangtao update20110509
						EditExcel(ws2,j, i+2, NumUtils.parseDouble(NumUtils.roundHalfup(list1.get(i)[j],2)) );
						
					}

				}

			}
			int startcol = 23;
			
			// 不参考核的绩效得分表 sheet2 右侧
			for (int i = 0; i < list2.size(); i++) {
			
				// 姓名
				EditExcel(ws2, startcol, i+3, list2.get(i)[0]);
				// 	工作质量考核
				//EditExcel(ws2, ++startcol, i+3, list2.get(i)[3]);
				EditExcel(ws2, ++startcol, i+3, NumUtils.parseDouble(list2.get(i)[19]) );
				
				// 专项考核
				//EditExcel(ws2, ++startcol, i+3, list2.get(i)[4]);
				EditExcel(ws2, ++startcol, i+3, NumUtils.parseDouble(list2.get(i)[20]) );
				
				// 未值班的天数
				//EditExcel(ws2, ++startcol, i+3, list2.get(i)[5]);
				EditExcel(ws2, ++startcol, i+3, NumUtils.parseDouble(list2.get(i)[21]) );
				
				// 原因
				//EditExcel(ws2, ++startcol, i+3, list2.get(i)[6]);
				
				startcol = (startcol == 26 ? 23 : startcol);
				
			}
			//////日均工作量
        WritableSheet ws3 = wwb.getSheet(2);
				
			// 参与考核的绩效得分表 sheet2 左侧
			for (int i = 0; i < list3.size(); i++) {
				for (int j = 0; j < 32; j++) {
						if (j == 0 ) {
						EditExcel(ws3,j, i+2, list1.get(i)[j]);
					}else if(j!=0){//by wangtao update20110509
						EditExcel(ws3,j, i+2, NumUtils.parseDouble(NumUtils.roundHalfup(list3.get(i)[j],2)) );
						
					}

				}

			}
			// 读取第5个sheet工作表数据录入
			WritableSheet ws5 = wwb.getSheet(5);

			// 班组考评得分
			for (int i = 0; i < listBzkp.size(); i++) {
				
				HashMap<String,Object> map = listBzkp.get(i);
			
				//考评分数
				EditExcel(ws5, 5, i+4, map.get("fs") == null ? 0 :NumUtils.parseDouble(map.get("fs").toString()));
			
			}
	// 写入Excel对象
			wwb.write();
			wwb.close();

		} catch (Exception e) {
			rtn = "1";
			e.printStackTrace();

		} finally {
			return rtn;
		}
	}

	/**
	 * 通过模版创建新Excel。
	 * 
	 * @param sheetnameM 模版路径及文件名称
	 * @param sheetnameO 生成模版的名称包括路径
	 * @param list 数据列表 数据格式 数组
	 */
	public String createExcel(final List<HashMap<String,Object>> list, List<String[]> list1, List<String[]> list2,List<String[]> list3,final List<HashMap<String,Object>> listbzkp) {
		
		String rtn = "0";
		
		try {

			// 模板文件路径
			String fileFullPathM =  Resources.getString("modelpath", "struts") + Resources.getString("jxcpmodel", "struts");
			// 生成文件名
			filenameen = DateFormatTool.format(new Date(), true).replace(" ", "").replaceAll("-", "").replaceAll(":", "") + RandomString.genRandomNum(8) + ".xls";
			// 创建生成文件路径
			FileUtils.createFolder(Resources.getString("jxcppath", "struts"));
			
			String fileFullPathO = Resources.getString("jxcppath", "struts") +  filenameen;

			// 读取模版文件形成输入流对象。
			if (this.is == null) {
				readExcel(fileFullPathM);
			}

			// 生成新表
			rtn = createNewExcel(fileFullPathO, list, list1, list2,list3,listbzkp);

		} catch (Exception e) {
			rtn = "1";
			e.printStackTrace();
		} finally {
			this.closeIs();
			return rtn;
		}
	}

	public List<HashMap<String, Object>> getJxcpUserList() {
		return jxcpUserList;
	}


	public List<HashMap<String, Object>> getJxcpKqList() {
		return jxcpKqList;
	}


	public List<HashMap<String, Object>> getJxcpGzzlkhList() {
		return jxcpGzzlkhList;
	}


	public List<HashMap<String, Object>> getJxcpZxkhList() {
		return jxcpZxkhList;
	}

}